

qui{
use "$jfqa_rep/10_CRSP_Controls.dta", clear

*merge in compustat data
sort permno monthyear
merge 1:1 permno monthyear using "$jfqa_rep/11_Compustat_Controls.dta"
drop if _merge==2
drop _merge 

*forward fill book equity, profit, asset
gsort permno -monthyear
qui{
forval i=1/11 {
replace bookeqty=bookeqty[_n+1] if permno==permno[_n+1] & missing(bookeqty) 
replace profit=profit[_n+1] if permno==permno[_n+1] & missing(profit) 
replace asset=asset[_n+1] if permno==permno[_n+1] & missing(asset)
}
}

gen bm_ratio=(bookeqty/mktcap)*1000

*****merge firm-specific char*****
*skewness
sort permno year month
merge 1:1 permno year month using "$jfqa_rep/12_Skew.dta"
drop if _merge==2
drop _merge

gen total_skew=skew
sort permno monthyear
by permno: replace total_skew=. if _n<12
drop skew
rename total_skew skew

*idiosyncratic volatility
sort permno year month
merge 1:1 permno monthyear using "$jfqa_rep/13_IVOL.dta"
drop if _merge==2
drop _merge

*illiquidity
sort permno year month
merge 1:1 permno year month using "$jfqa_rep/14_Spread_Volume.dta" 
drop if _merge==2
drop _merge
gen illiquid=(abs(ret)/dollar_vol)*1000000

*merge IBES estimates
rename ncusip cusip
sort cusip year month
merge m:1 cusip year month using "$jfqa_rep/15_IBES_Count.dta"
drop if _merge==2
drop _merge

sort permno monthyear
rename estimates ibes_est
replace ibes_est=0 if missing(ibes_est)
forval i=1/11 {
gen lag`i'est=L`i'.ibes_est
replace lag`i'est=0 if missing(lag`i'est)
}

gen ibes_est_12=ibes_est
forval i=1/11{
replace ibes_est_12=ibes_est_12+lag`i'est
}

forval i=1/11 {
drop lag`i'est
}

******merge rf
sort year month
merge m:1 year month using "$jfqa_rep/Monthly_RF.dta"
drop if _merge==2
drop _merge 

*define future returns
sort permno monthyear
gen excess=ret-rf/100
gen futexcess=F.excess
gen futret=F.ret

forval i=2/12 {
	gen fut`i'ret=F`i'.ret
	gen fut`i'excess=F`i'.excess
}

gen lagret=L.ret

gen excess_mid=mid_ret-rf/100
gen futexcess_mid=F.excess_mid
gen futret_mid=F.mid_ret

gen fut_mktcap=F.mktcap
gen lag_mktcap=L.mktcap

drop rf futrf

*merge crsp month-end data from daily data
sort permno year month
merge 1:1 permno year month using "$jfqa_rep/19_Append_Convex.dta"
keep if _merge==3
drop _merge

gen futret_skip=(1+futret)/(1+futret_1)-1

*merge in NYSE breakpoints
sort year month
merge m:1 year month using "$jfqa_rep/Market_Cap_Breakpoints.dta"
drop if _merge==2
drop _merge

*define NYSE buckets
qui{
gen nyse_quintile=1
replace nyse_quintile=2 if mktcap/1000>me_20
replace nyse_quintile=3 if mktcap/1000>me_40
replace nyse_quintile=4 if mktcap/1000>me_60
replace nyse_quintile=5 if mktcap/1000>me_80

gen nyse_decile=1
replace nyse_decile=2 if mktcap/1000>me_10
replace nyse_decile=3 if mktcap/1000>me_20
replace nyse_decile=4 if mktcap/1000>me_30
replace nyse_decile=5 if mktcap/1000>me_40
replace nyse_decile=6 if mktcap/1000>me_50
replace nyse_decile=7 if mktcap/1000>me_60
replace nyse_decile=8 if mktcap/1000>me_70
replace nyse_decile=9 if mktcap/1000>me_80
replace nyse_decile=10 if mktcap/1000>me_90
}
forval i=5(5)100{
	drop me_`i'
}

*clean data 
drop if prc<5 /*price filter*/
drop if missing(futret)
drop if missing(mktcap)
drop if missing(prc)

drop if exchcd<1
drop if exchcd>3 
drop if shrcd<10
drop if shrcd>11
drop exchcd shrcd
}

save "$jfqa_rep/20_Stock_Setup.dta", replace
